Unions & Joins

I. Unions

The UNION operator combines the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements and returns only distinct rows. If you want to include duplicates, you can use UNION ALL.

The result sets from each SELECT statement are essentially stacked on top of each other. Each result set contributes its rows to a combined result set, maintaining the column structure defined in the SELECT statements.

-- SYNTAX for UNION
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

Example 1: Find a list of employee and branch names.

SELECT first_name 
FROM employees; 
UNION
SELECT branch_name
FROM branches; 

Rules and Considerations

1. Same Number of Columns: All SELECT statements in a UNION must have the same number of columns in the result sets.

-- This will result in an error because the number of columns does not match.
SELECT name, department FROM employees
UNION
SELECT name FROM branches; 

2. Similar Data Types: The columns from each SELECT statement must have compatible data types. If they are not compatible, an error will occur.

-- This will result in an error if hire_date is a date and salary is numeric.
SELECT hire_date FROM employees
UNION
SELECT salary FROM employees; 

3. Column Names and Order: The column names in the result set are taken from the first SELECT statement.

SELECT name AS employee_name FROM employees
UNION
SELECT name FROM branches;

→ The column name in the result set will be employee_name because it is specified in the first SELECT statement.

4. UNION ALL is generally faster than UNION because it does not perform the additional step of removing duplicates. Use UNION ALL if you do not need to eliminate duplicates and want to optimize performance.

II. Joins

Joins are used to combine rows from two or more tables based on a related column between them. When you join tables in SQL, you're effectively creating a temporary, combined view of the data from which you can query.

graph TD
    A[Table A] --> C[Join Operation]
    B[Table B] --> C
    C --> D[Temporary Result Set]
    D --> E[Further Operations]
    E --> F[Query Result]
    style D fill:#f9f,stroke:#333,stroke-width:4px

Further operations here include WHERE, SELECT, GROUP BY, HAVING, ORDER BY, LIMIT, etc.

Types of Joins

Take two tables, employees, and branches

emp_id	first_name
1	Alice
2	Bob
3	Carol
4	Dave

branch_name	manager_id
North	1
South	3
East	5

1. Inner Join

Returns only the rows that have matching values in both tables. JOIN will default to INNER JOIN.

SELECT employees.emp_id, employees.first_name, branches.branch_name
FROM employees
INNER JOIN branches
ON employees.emp_id = branches.manager_id;

Results:

emp_id	first_name	branch_name
1	Alice	North
3	Carol	South

2. Left Join (Left Outer Join)

Returns all rows from the left table (employees) and the matched rows from the right table (branches). If no match is found, the result is NULL on the side of the branch.

SELECT employees.emp_id, employees.first_name, branches.branch_name
FROM employees
LEFT JOIN branches
ON employees.emp_id = branches.manager_id;

Results:

emp_id	first_name	branch_name
1	Alice	North
2	Bob	NULL
3	Carol	South
4	Dave	NULL

3. Right Join (Right Outer Join)

Returns all rows from the right table (branches) and the matched rows from the left table (employees). If no match is found, the result is NULL on the side of the employees.

SELECT employees.emp_id, employees.first_name, branches.branch_name
FROM employees
RIGHT JOIN branches
ON employees.emp_id = branches.manager_id;

Results:

emp_id	first_ name	branch_name
1	Alice	North
3	Carol	South
NULL	NULL	East

4. Full Join (Full Outer Join)

Returns all rows when there is a match in either the left or right table. If there is no match, the result is NULL on the side without a match.

SELECT employees.emp_id, employees.first_name, branches.branch_name
FROM employees
FULL JOIN branches
ON employees.emp_id = branches.manager_id;

Results:

emp_id	first_name	branch_name
1	Alice	North
2	Bob	NULL
3	Carol	South
4	Dave	NULL
NULL	NULL	East

5. Cross Join

Returns the Cartesian product of the two tables, meaning every possible combination of rows.

SELECT employees.emp_id, employees.first_name, branches.branch_name
FROM employees
CROSS JOIN branches;

Results:

emp_id	first_name	branch_name
1	Alice	North
1	Alice	South
1	Alice	East
2	Bob	North
2	Bob	South
2	Bob	East
3	Carol	North
3	Carol	South
3	Carol	East
4	Dave	North
4	Dave	South
4	Dave	East

5. Self Join

A self join is a join in which a table is joined with itself. This technique is useful when you need to compare rows within the same table or establish a relationship between rows of the same table.

Suppose we have an employees table with the following structure:

emp_id	first_name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2
5	Eve	2

By using self join, we can find each employee and their corresponding manager’s name.

Table Aliases:

• e represents the employees.
• m represents the managers.

JOIN Condition:

• ON e.manager_id = m.emp_id: Matches the manager_id from the e table (employees) with the emp_id from the m table (managers).

LEFT JOIN: Ensures that employees without managers (e.g., Alice) are still included in the result.

SELECT e.emp_id AS employee_id, e.first_name AS employee_name,
       m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;

Result:

employee_id	employee_name	smanager_name
1	Alice	NULL
2	Bob	Alice
3	Carol	Alice
4	Dave	Bob
5	Eve	Bob

Joining Multiple Tables

You can use Join to join more than 2 tables together.

select distinct m.firstname || ' ' || m.surname as member, 
		f.name as facility
from cd.members m
	join cd.bookings b on m.memid = b.memid
	join cd.facilities f on b.facid = f.facid;